﻿CREATE PROCEDURE s_PurchaseContract_CreateBatch
	@ContractHolderID int,
	@BatchName varchar(64),
	@BatchDesc nvarchar(4000),
	@Filter varchar(512),
	@BatchID int = 0
AS

IF NOT OBJECT_ID('GetLoansByFilter_') IS NULL DROP FUNCTION GetLoansByFilter_

DECLARE @FuncSQL varchar(max)
SELECT @FuncSQL = 
REPLACE(
REPLACE(Text,'SELECT ''''c',
'SELECT LoanID FROM v_PurchaseContract WHERE '+@Filter
),'GetLoansByFilter','GetLoansByFilter_'
)
FROM sys.objects o INNER JOIN syscomments c ON o.object_id = c.id WHERE o.Name = 'GetLoansByFilter'

EXEC(@FuncSQL)
BEGIN TRAN
IF @BatchID = 0 BEGIN
	INSERT INTO t_PurchaseBatch(ContractHolderID,Name,Description,Criteria)VALUES(@ContractHolderID,@BatchName,@BatchDesc,@Filter)
	SET @BatchID = SCOPE_IDENTITY()
END
INSERT INTO t_PurchaseBatch_Loans(BatchID,LoanID)
SELECT @BatchID,LBF.LoanID FROM GetLoansByFilter_ ()LBF
LEFT OUTER JOIN t_PurchaseBatch_Loans PB_Ls ON LBF.LoanID = PB_Ls.LoanID-- AND PB_Ls.BatchID = @BatchID
WHERE PB_Ls.LoanID IS NULL
IF @@ROWCOUNT = 0 OR @@ERROR <> 0 ROLLBACK TRAN ELSE COMMIT
RETURN @BatchID


